/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package tv.venta.dao;

import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import javax.ejb.Stateless;
import javax.persistence.Query;
import tv.global.dao.BaseDAO;
import tv.venta.vista.ItemHistoricoCuentaCorriente;
import tv.venta.vista.ItemPendienteCuentaCorriente;

/**
 *
 * @author Claudio
 */
@Stateless
public class CuentaCorrienteDAO extends BaseDAO {
    
    public List<ItemHistoricoCuentaCorriente> getAplicacionesEntreFechas(String nroCuenta, Date fDesde, Date fHasta){
        try {
            String sQuery = "SELECT CONVERT(VARCHAR, dbo.VTRMVH.VTRMVH_NROCTA) + CONVERT(VARCHAR, dbo.VTRMVH.VTRMVH_CODFOR) + CONVERT(VARCHAR,dbo.VTRMVH.VTRMVH_NROFOR) AS ID, "
                    + " dbo.VTRMVH.VTRMVH_NROCTA, dbo.VTRMVH.VTRMVH_SUCURS, dbo.VTRMVH.VTRMVH_FCHMOV, dbo.VTRMVH.VTRMVH_MODFOR, "
                    + " dbo.VTRMVC.VTRMVC_CUOTAS, dbo.VTRMVH.VTRMVH_CODFOR, dbo.VTRMVH.VTRMVH_NROFOR, SUM(CASE WHEN VTRMVC_IMPNAC >= 0 THEN VTRMVC_IMPNAC ELSE 0 END) AS DEBE, "
                    + " SUM(CASE WHEN VTRMVC_IMPNAC < 0 THEN  VTRMVC_IMPNAC * (- 1) ELSE 0 END) AS HABER, "
                    + "(SELECT GRCCBH_TITULO FROM dbo.GRCCBH WHERE (GRCCBH_MODCOM = dbo.VTRMVH.VTRMVH_MODFOR) AND (GRCCBH_CODCOM = dbo.VTRMVH.VTRMVH_CODFOR)) AS COMPROBANTE "
                    + " FROM dbo.VTRMVH INNER JOIN "
                    + "    dbo.VTRMVC ON dbo.VTRMVH.VTRMVH_MODFOR = dbo.VTRMVC.VTRMVC_MODFOR "
                    + "               AND dbo.VTRMVH.VTRMVH_CODFOR = dbo.VTRMVC.VTRMVC_CODFOR "
                    + "               AND dbo.VTRMVH.VTRMVH_NROFOR = dbo.VTRMVC.VTRMVC_NROFOR "
                    + " WHERE (dbo.VTRMVC.VTRMVC_CODFOR NOT LIKE 'AP%') "
                    + " AND (dbo.VTRMVC.VTRMVC_CODFOR NOT LIKE 'AAP%') "
                    + " AND (dbo.VTRMVH.VTRMVH_DEBAJA = 'N') "
                    + " AND (VTRMVH_NROCTA = ?1) AND (VTRMVH_FCHMOV BETWEEN ?2 AND ?3) "
                    + " GROUP BY dbo.VTRMVH.VTRMVH_NROCTA, dbo.VTRMVH.VTRMVH_SUCURS, dbo.VTRMVH.VTRMVH_NROFOR, "
                    + " dbo.VTRMVH.VTRMVH_FCHMOV, "
                    + " dbo.VTRMVH.VTRMVH_MODFOR, dbo.VTRMVC.VTRMVC_CUOTAS, dbo.VTRMVH.VTRMVH_CODFOR, "
                    + " dbo.VTRMVH.VTRMVH_NROCTA + dbo.VTRMVH.VTRMVH_NROFOR "
                    + " ORDER BY VTRMVH.VTRMVH_FCHMOV ";

            /**
             *
             * + " AND (VTRMVH_NROCTA = '"+nroCuenta+"') "
                    + " AND (VTRMVH_FCHMOV BETWEEN '"+fDesde+"' AND '"+fHasta+"') "
            String query = "SELECT i FROM ItemHistoricoCuentaCorriente i "
                    + "WHERE (i.nrocta = :nrocta) "
                    + "AND (i.fchmov BETWEEN :fDesde AND :fHasta) "
                    + "ORDER BY i.fchmov";
            Query q = em.createQuery(query);             *
            q.setParameter("nrocta", nroCuenta);
            q.setParameter("fDesde", fDesde);
            q.setParameter("fHasta", fHasta);
            */

            Query q = getEntityManager().createNativeQuery(sQuery, ItemHistoricoCuentaCorriente.class);

            q.setParameter(1, nroCuenta);
            q.setParameter(2, fDesde);
            q.setParameter(3, fHasta);
            return q.getResultList();

        } catch (Exception e) {
            System.err.println("Error al consultar Aplicaciones entre fechas");
            e.printStackTrace();
            return new ArrayList<ItemHistoricoCuentaCorriente>();
        }
    }

    public List<ItemPendienteCuentaCorriente> getPendientesByNroCuenta(String nroCuenta,String debeHaber){
        try {
            
            String sQuery = "SELECT CONVERT(VARCHAR(6), VTRMVC_NROCTA) + CONVERT(VARCHAR(6), VTRMVC_CODAPL) + CONVERT(VARCHAR, VTRMVC_NROAPL) AS ID, "
                    + " VTRMVC_NROCTA AS NROCTA, VTRMVC_CODAPL AS COD_COMPROBANTE, VTRMVC_NROAPL AS NUMERO, VTRMVC_CUOTAS AS CUOTAS, "
                    + " VTRMVC_FCHVNC AS VENCIMIENTO, SUM(VTRMVC_IMPNAC) AS PENDIENTE,"
                    + " (SELECT GRCCBH_TITULO FROM dbo.GRCCBH WHERE(GRCCBH_MODCOM = AP.VTRMVC_MODAPL) "
                    + " AND (GRCCBH_CODCOM = AP.VTRMVC_CODAPL)) AS COMPROBANTE "
                    + " FROM dbo.VTRMVC AS AP "
                    + " WHERE AP.VTRMVC_NROCTA = ?1 "
                    + " GROUP BY VTRMVC_NROCTA, VTRMVC_MODAPL, VTRMVC_CODAPL, VTRMVC_NROAPL, VTRMVC_CUOTAS, VTRMVC_FCHVNC "
                    + " HAVING " + (debeHaber.equals("D") ? "(SUM(VTRMVC_IMPNAC) > 0) " : "(SUM(VTRMVC_IMPNAC) < 0) ");


            // <editor-fold defaultstate="collapsed" desc="comment">
//            String query = "SELECT i FROM ItemPendienteCuentaCorriente i "
//                    + "WHERE (i.nrocta = :nrocta) "
//                    + (debeHaber.equals("D") ? "AND (i.pendiente > 0) " : "AND (i.pendiente < 0) ")
//                    + "ORDER BY i.vencimiento ";
            //</editor-fold>
            Query q = getEntityManager().createNativeQuery(sQuery, ItemPendienteCuentaCorriente.class);
            q.setParameter(1, nroCuenta);
            
            return q.getResultList();
        } catch (Exception e) {
            System.err.println("Error al consultar pendientes por nro cuenta");
//            e.printStackTrace();
            return new ArrayList<ItemPendienteCuentaCorriente>();
        }        
    }

    public BigDecimal getSaldoAFecha(String nrocta, Date fDesde){
        
        try {
            String query = "SELECT SUM(a.impnac) FROM AplicacionCuentaCorriente a "
                    + "WHERE (a.cliente.nrocta = :nrocta) "
                    + "AND a.fchmov <= :fDesde ";
            
            Query q = getEntityManager().createQuery(query);
            q.setParameter("nrocta", nrocta);
            q.setParameter("fDesde", fDesde);
            return (BigDecimal) q.getSingleResult();
        } catch (Exception e) {
            System.err.println("Error al consultar pendientes por nro cuenta");
            e.printStackTrace();
            return BigDecimal.ZERO;
        }
        
    }
 
}
